<?php
/**
 * EXCEL 处理类
 * 
 * @author Flan 20130109
 *
 */
class fMyExcel {
	
	public $m_bDebug = false;
	
	/**
	 * @var string 文件路径
	 */
	public $m_sInputName = '';
	
	/**
	 * @var array excel 的数据
	 */
	public $m_aData = array();
	
	/**
	 * @var array 错误信息
	 */
	public $m_aError = array();
	
	/**
	 * @var array 执行信息
	 */
	private $m_aMsg= array();
	
	/**
	 * @var string 文件路径 
	 */
	private $m_sFile = '';
	
	/**
	 * @var string 执行时间
	 */
	private $m_aTime = array();
	
	/**
	 * @var array Excel Config
	 */
	private $m_aConfigExcel = array();
	
	
	/**
	 * init
	 *
	 * @param string $sInputName input name
	 * @param array $aConfigExcel array('titleRowNum' => '标题所占行数', 
										'titltFields' => '字段Field数组：array('f1', 'f2')', 
										'titltFieldsType' => '字段类型 默认为string i为 int：array(1 => 'i', 5 => 'i')'
								);
	 * @param boolean $bDebug
	 */
	public function __construct($sInputName, $aConfigExcel=array(), $bDebug=false){
		$this->m_sInputName = $sInputName;
		$this->m_bDebug = $bDebug;
		$this->m_aConfigExcel = $aConfigExcel;
	}
	
	/**
	 * 生成一个上传页面的html 用于测试
	 * 
	 * @access public
	 * @param string $sPostUrl  post url
	 * @return string
	 */
	public function creatUpdateFileHtml($sPostUrl){
		$sHtml = '';
		$sHtml .= '<form enctype="multipart/form-data" action="'.$sPostUrl.'" method="POST">';
		$sHtml .= '<input type="hidden" name="MAX_FILE_SIZE" value="30000000" />';
		$sHtml .= '<input name="'.$this->m_sInputName.'" type="file" />'; // userfile
		$sHtml .= '<input type="submit" value="Send File" />';
		$sHtml .= '</form>';
		
		return $sHtml;
	}
	
	/**
	 * 上传文件信息
	 *
	 * @access private
	 * @return void
	 */
	public function checkFile(){
		
		$aUserfile = $_FILES[$this->m_sInputName];
		$this->m_sFile = $aUserfile['tmp_name'];
		
		// 错误处理
		$aError = array(
			0 => '没有错误发生，文件上传成功。',
			1 => '上传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值',
			2 => '上传文件的大小超过了 HTML 表单中 MAX_FILE_SIZE 选项指定的值',
			3 => '文件只有部分被上传',
			4 => '没有文件被上传',
			6 => '找不到临时文件夹。',
			7 => '文件写入失败。',
		);
		$nError = $aUserfile['error'];
		
		if ($nError > 0){
			$this->m_aError[] = $aError[$nError];
			$this->showMessage();
			return false;
		}
		
		// 输出上传文件信息
		$this->m_aMsg[] = '-------------上传文件的信息--------------';
		foreach ((array) $aUserfile as $sKey => $sVal){
			$this->m_aMsg[] = $sKey.': '.$sVal;
		}
		$this->m_aMsg[] = '============没有错误发生，文件上传成功。';
		
		return $this->showExcels();
	}
	
	/**
	 * 上传excel数据信息
	 *
	 * @access private
	 * @return void
	 */
	private function showExcels(){
		
		$this->recordTime('读取EXCEL开始');
		
		set_time_limit(0);
		include 'reader.php';
		
		$pExcel = new Spreadsheet_Excel_Reader();
		$pExcel->setOutputEncoding('utf-8');
		$pExcel->read($this->m_sFile);
		$this->m_aData = $pExcel->sheets[0]['cells'];
		
		$this->recordTime('读取EXCEL结束');

		if (count($this->m_aData) == 0){
			$this->m_aError[] = '============EXCEL读取失败！';
			$this->showMessage();
			return false;
		} 
		
		$this->m_aMsg[] = '============Excel 读取成功！';
		$this->m_aMsg[] = '-------------上传EXCEL的信息--------------';
		$this->m_aMsg[] = '数据总条数：'.count($this->m_aData);
		
		
		$this->showMessage();
		
		// 处理数据
		if (count($this->m_aConfigExcel) > 0){
			$nTitleRowNum = $this->m_aConfigExcel['titleRowNum'];
			$aFields = $this->m_aConfigExcel['titltFields'];
			$aFieldTyprs = $this->m_aConfigExcel['titltFieldsType'];
			
			for ($i=0;$i<$nTitleRowNum; $i++){
				array_shift($this->m_aData);
			}
			
			foreach ($this->m_aData as $key => $aData){
				foreach ($aData as $k => $s){
					unset($aData[$k]);
					$aData[$aFields[$k - 1]] = ($aFieldTyprs[$k] == 'i') ? intval(trim($s)) : trim($s);
				}
				$this->m_aData[$key] = $aData;
			}
		}
// 		print_r($this->m_aData);
		
		return ture;
	}
	
	
	/**
	 * 输出message
	 *
	 * @access private
	 * @return void
	 */
	private function showMessage(){
		if (!$this->m_bDebug){
			return false;
		}
		echo '-------------------------------------------------------------------------begin';
		echo '<h3>执行过程：</h3>';
		foreach ($this->m_aMsg as $sS){
			echo $sS;
			echo '<br />';
		}
	
		echo '<h3>失败信息：</h3>';
		foreach ($this->m_aError as $sS){
			echo $sS;
			echo '<br />';
		}
		
		$this->showTime();
		echo '-------------------------------------------------------------------------end';
	
		echo '<br />';echo '<br />';
	}
	
	/**
	 * 记录time
	 *
	 * @access public
	 * @param string 记录名称
	 * @return void
	 */
	public function recordTime($sRecordName){
		$this->m_aTime[] = array(
			'name' => $sRecordName,
			'time' => date('Y-m-d h-i-s'),
			'memory' => memory_get_usage(),
		);
	}
	
	/**
	 * 输出time
	 *
	 * @access public
	 * @return void
	 */
	public function showTime(){
		echo '<h3>时间：</h3>';
		foreach ($this->m_aTime as $aD){
			echo $aD['name'].'|';
			echo 'time:'.$aD['time'].' ';
			echo 'memory:'.$aD['memory'];
			echo '<br />';
		}
	}
	
	
	
	/**
	 * 生成 INSERT sql
	 * 
	 * @param String $tableName 表名
	 * @param array $aData 字段对应值数组
	 * 
	 * @return sql
	 */
	static public function makeSql($tableName, $aData){
		$fields = '';
		$values = '';
		foreach($aData as $k=>$v) {
			$fields .= $fields ? ','.$k : $k;
			$values .= $values ? ',\''.$v.'\'' : '\''.$v.'\'';
		}
		return 'INSERT INTO '.$tableName.' ('.$fields.') VALUES ('.$values.');';
	}
}